﻿
using System.Data;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace DataObject.News
{
    internal class NewsDAL: DAL
    {
        /***************************************
        = Back End
        -------------------------------------- */
        internal static void News_Create(NewsObject o)
        {
            SqlHelper.ExecuteScalar(Conn, "News_Insert", o.News_ID
                                                                    ,o.Cat_ID
                                                                    ,o.News_Title
                                                                    ,o.News_Sapo
                                                                    ,o.News_Subtitle
                                                                    ,o.News_Image
                                                                    ,o.News_ImageHomePage      
                                                                    ,o.News_Content
                                                                    ,o.News_Status
                                                                    ,o.News_Type
                                                                    ,o.News_UserID
                                                                    ,o.News_HomePageFocus
                                                                    ,o.News_ListFocus
                                                                    ,o.News_BoxFocus
                                                                    ,o.News_PenName
                                                                    ,o.News_Video
                                                                    ,o.News_VideoTitle
                                                                    ,o.News_PublishDate        
                                                                    ,o.News_CreatedBy);
        }

        internal static void News_Update(NewsObject o)
        {
            SqlHelper.ExecuteNonQuery(Conn, "News_Update", o.News_ID
                                                          ,o.Cat_ID
                                                          ,o.News_Title
                                                          ,o.News_Sapo
                                                          ,o.News_Subtitle
                                                          ,o.News_Image
                                                          ,o.News_ImageHomePage      
                                                          ,o.News_Content
                                                          ,o.News_Status
                                                          ,o.News_Type
                                                          ,o.News_UserID
                                                          ,o.News_HomePageFocus
                                                          ,o.News_ListFocus
                                                          ,o.News_BoxFocus
                                                          ,o.News_PenName
                                                          ,o.News_Video
                                                          ,o.News_VideoTitle
                                                          ,o.News_PublishDate        
                                                          ,o.News_ApprovedDate
                                                          ,o.News_EditedBy
                                                          ,o.News_ApprovedBy);
        }

        internal static void News_Update_Status(long id, int status, string approvedBy)
        {
            SqlHelper.ExecuteNonQuery(Conn, "News_Update_Status", id, status, approvedBy);
        }

        internal static IDataReader News_GetById(long id)
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT TOP 1 * ");
            sb.AppendFormat("FROM [{0}]", Constants.Table_News.TableName);
            sb.AppendFormat("WHERE [{0}] = {1}", Constants.Table_News.News_ID, id);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static int News_Count(string keywords, string sd, string ed, int type, int status)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT([{0}]) ", Constants.Table_News.News_ID);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_News.TableName);
            sb.AppendFormat("WHERE [{0}] = {2} AND [{1}] = {3} ", Constants.Table_News.News_Status, Constants.Table_News.News_Type, status, type);
            sb.AppendFormat("AND ('{1}' = '' OR [{0}] LIKE '%{1}%') ", Constants.Table_News.News_Title, keywords);
            sb.AppendFormat("AND [{0}] BETWEEN '{1}' AND '{2}' ", Constants.Table_News.News_PublishDate, sd, ed);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static int News_GetStatistic(int type, int status)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT([{0}]) ", Constants.Table_News.News_ID);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_News.TableName);
            sb.AppendFormat("WHERE [{0}] = {2} AND [{1}] = {3} ", Constants.Table_News.News_Status, Constants.Table_News.News_Type, status, type);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader News_GetListByTypeByStatusPaging(string keywords, string sd, string ed, int type, int status, int pageIndex, int pageSize)
        {
            sb = new StringBuilder();
            sb.AppendLine("DECLARE @StartRow INT");
            sb.AppendFormat("SET @StartRow = ({0} - 1) * {1} + 1 ", pageIndex, pageSize);
            sb.AppendLine("DECLARE @EndRow INT");
            sb.AppendFormat("SET @EndRow = {0} * {1}; ", pageIndex, pageSize);
            sb.AppendLine("WITH temp AS");
            sb.AppendLine("(");
            sb.AppendLine("SELECT TOP(@EndRow) * ");
            sb.AppendFormat(", ROW_NUMBER() OVER (ORDER BY [{0}] DESC) AS 'RowNumber' ", Constants.Table_News.News_PublishDate);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_News.TableName);
            sb.AppendFormat("WHERE [{0}] = {2} AND [{1}] = {3} ", Constants.Table_News.News_Status, Constants.Table_News.News_Type, status, type);
            sb.AppendFormat("AND ('{1}' = '' OR [{0}] LIKE '%{1}%') ", Constants.Table_News.News_Title, keywords);
            sb.AppendFormat("AND [{0}] BETWEEN '{1}' AND '{2}'", Constants.Table_News.News_PublishDate, sd, ed);
            sb.AppendLine(")");
            sb.AppendLine("SELECT * FROM temp ");
            sb.AppendLine("WHERE RowNumber BETWEEN @StartRow AND @EndRow");
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }
    }
}
